﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OracleClient;
using EntitiesLayer.Entities;
using System.Data;

namespace DataLayer.DAO
{
    public class SugerenciaDAO
    {
        private Connection.Connection cn;
        private OracleConnection objCn;

        public SugerenciaDAO()
        {
            cn = new Connection.Connection();
            objCn = cn.Conecta();
        }



        public List<Sugerencia> Listado_Sugerencia(string codigo)
         {
             List<Sugerencia> lstObject = new List<Sugerencia>();
             try
             {
                 OracleCommand cmd = new OracleCommand("Listado_Sugerencia", objCn);
                 objCn.Open();
                 cmd.Connection = objCn;
                 cmd.CommandTimeout = 60;
                 cmd.CommandType = CommandType.StoredProcedure;
                 cmd.Parameters.Add(new OracleParameter("RECORDSET", OracleType.Cursor, 100, ParameterDirection.Output, null, DataRowVersion.Current, false, null));
                 cmd.Parameters.Add(new OracleParameter("Rsug_Cod_Sug", OracleType.NVarChar, 10, ParameterDirection.Input, false, 10, 0, null, DataRowVersion.Current, codigo));                 

                 using (IDataReader oRead = cmd.ExecuteReader(CommandBehavior.SingleResult))
                 {
                     if (oRead != null)
                     {
                         while (oRead.Read())
                         {
                             Sugerencia oObj = new Sugerencia();

                             if (!oRead.IsDBNull(oRead.GetOrdinal("Vsug_Desc_Sug")))
                             {
                                 if (!string.IsNullOrEmpty(oRead.GetString(oRead.GetOrdinal("Vsug_Desc_Sug")).ToString()))
                                 {
                                     oObj.Vsug_Desc_Sug = oRead.GetString(oRead.GetOrdinal("Vsug_Desc_Sug"));
                                 }
                             }

                             if (!oRead.IsDBNull(oRead.GetOrdinal("Vsug_Com_Adi")))
                             {
                                 if (!string.IsNullOrEmpty(oRead.GetString(oRead.GetOrdinal("Vsug_Com_Adi")).ToString()))
                                 {
                                     oObj.Vsug_Com_Adi = oRead.GetString(oRead.GetOrdinal("Vsug_Com_Adi"));
                                 }
                             }

                             if (!oRead.IsDBNull(oRead.GetOrdinal("Vsed_Desc_Sede")))
                             {
                                 if (!string.IsNullOrEmpty(oRead.GetString(oRead.GetOrdinal("Vsed_Desc_Sede")).ToString()))
                                 {
                                     oObj.Vsed_Desc_Sede = oRead.GetString(oRead.GetOrdinal("Vsed_Desc_Sede"));
                                 }
                             }

                             if (!oRead.IsDBNull(oRead.GetOrdinal("Vneg_Desc_Uni_Neg")))
                             {
                                 if (!string.IsNullOrEmpty(oRead.GetString(oRead.GetOrdinal("Vneg_Desc_Uni_Neg")).ToString()))
                                 {
                                     oObj.Vneg_Desc_Uni_Neg = oRead.GetString(oRead.GetOrdinal("Vneg_Desc_Uni_Neg"));
                                 }
                             }

                             if (!oRead.IsDBNull(oRead.GetOrdinal("Vtpsug_Des_Tip_Sug")))
                             {
                                 if (!string.IsNullOrEmpty(oRead.GetString(oRead.GetOrdinal("Vtpsug_Des_Tip_Sug")).ToString()))
                                 {
                                     oObj.Vtpsug_Des_Tip_Sug = oRead.GetString(oRead.GetOrdinal("Vtpsug_Des_Tip_Sug"));
                                 }
                             }

                             if (!oRead.IsDBNull(oRead.GetOrdinal("Vare_Desc_Area")))
                             {
                                 if (!string.IsNullOrEmpty(oRead.GetString(oRead.GetOrdinal("Vare_Desc_Area")).ToString()))
                                 {
                                     oObj.Vare_Desc_Area = oRead.GetString(oRead.GetOrdinal("Vare_Desc_Area"));
                                 }
                             }


                             if (!oRead.IsDBNull(oRead.GetOrdinal("Vgen_Desc_Mot_Gen")))
                             {
                                 if (!string.IsNullOrEmpty(oRead.GetString(oRead.GetOrdinal("Vgen_Desc_Mot_Gen")).ToString()))
                                 {
                                     oObj.Vgen_Desc_Mot_Gen = oRead.GetString(oRead.GetOrdinal("Vgen_Desc_Mot_Gen"));
                                 }
                             }


                             if (!oRead.IsDBNull(oRead.GetOrdinal("Vser_Desc_Serv")))
                             {
                                 if (!string.IsNullOrEmpty(oRead.GetString(oRead.GetOrdinal("Vser_Desc_Serv")).ToString()))
                                 {
                                     oObj.Vser_Desc_Serv = oRead.GetString(oRead.GetOrdinal("Vser_Desc_Serv"));
                                 }
                             }





                             
                             lstObject.Add(oObj);
                             oObj = null;
                         }
                     }
                 }

             }
             catch (Exception ex)
             {
                 objCn.Dispose();
             }
             finally
             {
                 objCn.Close();
             }

             return lstObject;

         }


        public string insertSugerencia(Sugerencia s)
        {
            string resultado = "";

            using (OracleCommand cmd = new OracleCommand("INSERTSUGERENCIA", objCn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                OracleParameter o1 = new OracleParameter("R_CODIGO_GENERADO", OracleType.VarChar);
                o1.Size = 30;
                o1.Direction = ParameterDirection.Output;

                OracleParameter s1 = new OracleParameter("R_DESC_SUG", OracleType.VarChar, 4000);
                s1.Value = s.DesSugerencia;

                OracleParameter s2 = new OracleParameter("R_COM_ADI", OracleType.VarChar, 4000);
                s2.Value = s.ComAdiSugerencia;

                OracleParameter s3 = new OracleParameter("R_COD_TIP_SUG", OracleType.VarChar, 10);
                s3.Value = s.CodTipoSugerencia;

                OracleParameter s4 = new OracleParameter("R_COD_SEDE", OracleType.VarChar, 10);
                s4.Value = s.CodSede;

                OracleParameter s5 = new OracleParameter("R_COD_UNI_NEG", OracleType.VarChar, 10);
                s5.Value = s.CodUniNego;

                OracleParameter s6 = new OracleParameter("R_COD_SERV", OracleType.VarChar, 10);
                s6.Value = s.CodServicio;

                OracleParameter s7 = new OracleParameter("R_COD_AREA", OracleType.VarChar, 10);
                s7.Value = s.CodArea;

                OracleParameter s8 = new OracleParameter("R_COD_MOT_GEN", OracleType.VarChar, 10);
                s8.Value = s.CodMotivoGeneral;

                OracleParameter s9 = new OracleParameter("R_COD_MOT_ESP", OracleType.VarChar, 10);
                s9.Value = s.CodMotivoEspecifico;

                OracleParameter s10 = new OracleParameter("R_COD_TIPO_SOLICITUD", OracleType.VarChar, 10);
                s10.Value = s.CodTipoSolicitud;

                OracleParameter s11 = new OracleParameter("R_Fecha_Registro", OracleType.DateTime);
                s11.Value = s.FechaRegistroSUG;

                OracleParameter s12 = new OracleParameter("R_USU_NRO_DOCUMENTO", OracleType.VarChar, 20);
                s12.Value = s.CodUsuario;

                cmd.Parameters.Add(o1);
                cmd.Parameters.Add(s1);
                cmd.Parameters.Add(s2);
                cmd.Parameters.Add(s3);
                cmd.Parameters.Add(s4);
                cmd.Parameters.Add(s5);
                cmd.Parameters.Add(s6);
                cmd.Parameters.Add(s7);
                cmd.Parameters.Add(s8);
                cmd.Parameters.Add(s9);
                cmd.Parameters.Add(s10);
                cmd.Parameters.Add(s11);
                cmd.Parameters.Add(s12); 

                try
                {
                    objCn.Open();

                    cmd.ExecuteNonQuery();

                    resultado = o1.Value.ToString();
                }
                catch (Exception ex)
                {
                    objCn.Dispose();
                }
                finally
                {
                    objCn.Close();
                }
            }
            return resultado;
        }

    }
}
